unit hadesqldbdataset;

{$mode objfpc}{$H+}

interface

uses
  Classes,
  SysUtils,
  hadebasedataset,
  hadebaseobject,
  hadeconnectionmanager,
  sqldb,
  hademapping;

type

  { THadeSQLDBDataSet }
  { SQLDB implementation of IHadeDataSet}
  THadeSQLDBDataSet = class(THadeBaseDataSet, IHadeDataSet)
  private
    FQuery: TSQLQuery;
  protected
    procedure SetSQL(AValue: TStrings);
    function GetSQL: TStrings;

    procedure SetValueByName(const AProp: string; const AVal: variant);
    function getValueByName(const AColName: string): variant;

    procedure setStringByName(const AProp: string; const AVal: string);
    function getStringByName(const AColName: string): string;

    function getIntegerByName(const AColName: string): integer;
    procedure setIntegerByName(const AProp: string; const AVal: integer);

    function getDoubleByName(const AColName: string): double;
    procedure setDoubleByName(const AProp: string; const AVal: double);

    function getDateTimeByName(const AColName: string): TDateTime;
    procedure setDateTimeByName(const AProp: string; const AVal: TDateTime);
  public
    function Count: integer;

    procedure Open;
    procedure Close;
    procedure ExecSQL;
    procedure ExecuteMultiple(Asql: string);

    procedure Next;
    procedure First;
    procedure Last;
    procedure Prior;
    function EOF: boolean;

    function getLastInsertID(ATableName:String):int64;

    constructor Create(const AConnection: THadeConnection);override;
    destructor Destroy; override;
  end;

implementation

uses
  DB,
  hadeexception,
  hadebroker;

{ THadeSQLDBDataSet }

procedure THadeSQLDBDataSet.SetSQL(AValue: TStrings);
begin
  FQuery.SQL := TStringList(AValue);
end;

function THadeSQLDBDataSet.GetSQL: TStrings;
begin
  Result := FQuery.SQL;
end;

procedure THadeSQLDBDataSet.SetValueByName(const AProp: string; const AVal: variant);
begin
  FQuery.ParamByName(AProp).Value := AVal;
end;

function THadeSQLDBDataSet.getValueByName(const AColName: string): variant;
begin
  Result := FQuery.FieldByName(AColName).Value;
end;

procedure THadeSQLDBDataSet.setStringByName(const AProp: string; const AVal: string);
begin
  FQuery.ParamByName(AProp).AsString := AVal;
end;

function THadeSQLDBDataSet.getStringByName(const AColName: string): string;
begin
  Result := FQuery.FieldByName(AColName).AsString;
end;

function THadeSQLDBDataSet.getIntegerByName(const AColName: string): integer;
begin
  Result := FQuery.FieldByName(AColName).AsInteger;
end;

procedure THadeSQLDBDataSet.setIntegerByName(const AProp: string; const AVal: integer);
begin
  FQuery.ParamByName(AProp).AsInteger := AVal;
end;

function THadeSQLDBDataSet.getDoubleByName(const AColName: string): double;
begin
  Result := FQuery.FieldByName(AColName).AsFloat;
end;

procedure THadeSQLDBDataSet.setDoubleByName(const AProp: string; const AVal: double);
begin
  FQuery.ParamByName(AProp).AsFloat := AVal;
end;

function THadeSQLDBDataSet.getDateTimeByName(const AColName: string): TDateTime;
begin
  Result := FQuery.FieldByName(AColName).AsDateTime;
end;

procedure THadeSQLDBDataSet.setDateTimeByName(const AProp: string;
  const AVal: TDateTime);
begin
  FQuery.ParamByName(AProp).AsDateTime := AVal;
end;

function THadeSQLDBDataSet.Count: integer;
begin
  Result := FQuery.RecordCount;
end;

procedure THadeSQLDBDataSet.Open;
begin
  try
    Fquery.prepare;
    Fquery.Open;
  except
    on E: EDatabaseError do
      raise EHadeDatabase.Create(E.Message);
  end;
end;

procedure THadeSQLDBDataSet.Close;
begin
  FQuery.Close;
end;

procedure THadeSQLDBDataSet.ExecSQL;
begin
  try
    Fquery.ExecSQL;
  except
    on E: EDatabaseError do
      raise EHadeDatabase.Create(E.Message);
  end;
end;

procedure THadeSQLDBDataSet.ExecuteMultiple(Asql: string);
var
  sqlScript: TSQLScript;
begin
  sqlScript := TSQLScript.Create(nil);
  try
    sqlScript.DataBase := FQuery.DataBase;
    sqlScript.Transaction := FQuery.Transaction;

    sqlScript.Script.Text := ASql;
    try
      sqlScript.ExecuteScript;
    except
      on E: EDatabaseError do
        raise EHadeDatabase.Create(E.Message);
    end;
  finally
    FreeAndNil(sqlScript);
  end;
end;

procedure THadeSQLDBDataSet.Next;
begin
  FQuery.Next;
end;

procedure THadeSQLDBDataSet.First;
begin
  FQuery.First;
end;

procedure THadeSQLDBDataSet.Last;
begin
  FQuery.Last;
end;

procedure THadeSQLDBDataSet.Prior;
begin
  FQuery.Prior;
end;

function THadeSQLDBDataSet.EOF: boolean;
begin
  Result := FQuery.EOF;
end;

function THadeSQLDBDataSet.getLastInsertID(ATableName: String): int64;
var
  pk,sql: String;
begin
  if FConnection.Broker = SQLDB_SQLITE then
    Result := FConnection.getLastInsertID(ATableName)
  else
  begin
    pk := FMappingManager.TableByName(ATableName).getPK.ColumnName;
    sql:='SELECT MAX('+pk+') as OID FROM '+ATableName;
    FQuery.SQL.Clear;
    FQuery.SQL.Text:=sql;
    FQuery.Open;

    Result := FQuery.Fields[0].AsInteger;
  end;
end;

constructor THadeSQLDBDataSet.Create(const AConnection: THadeConnection);
begin
  inherited create(AConnection);
  FQuery := TSQLQuery.Create(nil);
  FConnection.setDataSet(FQuery);
  FQuery.PacketRecords := -1;
end;

destructor THadeSQLDBDataSet.Destroy;
begin
  FreeAndNil(FQuery);
  inherited Destroy;
end;

end.

